package com.bdcom.autotest.interaction.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.bdcom.autotest.interaction.bean.TestLog;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import com.bdcom.autotest.interaction.bean.DBInfoBean;

public class DBUtil {
	
	private static Logger log = LoggerFactory.getLogger(DBUtil.class);
	
//	select ap.id , ap.deviceCode , ap.productNo, ap.subWorkOrder,ap.status , subo.productType , subo.LineFlag,subo.workOrder
//	from assemblyproduct ap , assemblysubworkorder subo
//	where ap.productNo in ('SM55011249', 'SM55011250' , 'SM55011251' ,'SM55011252')
//	and subo.sid=ap.subWorkOrder
//--
//	select ap.id , ap.deviceCode , ap.productNo, ap.subWorkOrder,ap.status , subo.productType , subo.LineFlag,subo.workOrder
//	from assemblyproduct ap , assemblysubworkorder subo
//	where ap.productNo in ('20065000282', '20065000283' , '20065000284' ,'20065000285')
//	and subo.sid=ap.subWorkOrder
	
//	select od.id, od.WorkOrderNo , od.Create_Time from assemblyworkorder od where id in (13171)  
	
//	select pt.productNo , pt.prodtype, pt.proddscr from producttype pt where productNo in ('CBSWI-JCA0985A')  
	
	
	
	public static DBInfoBean getInfoForChassis( String sn ){
		
		Connection conn = PmgrDB.getConn();
		
		DBInfoBean queryBaseInfo = queryBaseInfo( sn  , conn);
		if(queryBaseInfo!=null ) {
			
			queryWorkOrderInfo(  queryBaseInfo , conn );
			queryProdInfo(  queryBaseInfo , conn );
		}
		
		log.debug( "基础信息 ： " + queryBaseInfo );
		PmgrDB.closeRes(conn);
		
		return queryBaseInfo;
	}
	
	
	
	private static void queryProdInfo(DBInfoBean baseInfo, Connection conn) {
		String sql = " 	select pt.productNo , pt.prodtype, pt.proddscr from producttype pt where productNo=?  ";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString( 1,  baseInfo.getProductType() );
			rs = ps.executeQuery();
			if(  rs.next()   ) {
				String productType = rs.getString(1);
				String prodType = rs.getString(2);
				String prodDscr = rs.getString(3);
				
				baseInfo.setProdType(prodType);
				baseInfo.setProdDscr(prodDscr);
			}
		} catch (SQLException e) {
			log.error( "" ,e );
		}finally {
			PmgrDB.closeRes(ps , rs);
		}
	}
	private static void queryWorkOrderInfo(DBInfoBean baseInfo, Connection conn) {
		
		String sql = "	select od.id , od.WorkOrderNo , od.Create_Time from assemblyworkorder od where id = ?   ";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1,  baseInfo.getWorkOrderId() );
			rs = ps.executeQuery();
			if(  rs.next()   ) {
				long odId = rs.getLong(1);
				String workOrderNo = rs.getString(2);
				Date date = rs.getDate(3 );
				
				baseInfo.setWorkOrderNo(workOrderNo);
				baseInfo.setCreate_Time(date);
			}
		} catch (SQLException e) {
			log.error( "" ,e );
		}finally {
			PmgrDB.closeRes(ps , rs);
		}
	}
	private static DBInfoBean queryBaseInfo(String sn, Connection conn) {
		
		String sql = "	select ap.id , ap.deviceCode , ap.productNo, ap.subWorkOrder,ap.status , subo.productType , subo.LineFlag,subo.workOrder " + 
				" from assemblyproduct ap , assemblysubworkorder subo "+
				" where (ap.productNo=? or ap.deviceCode=? ) "  +
				" and subo.sid=ap.subWorkOrder ";
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sn);
			ps.setString(2, sn);
			rs = ps.executeQuery();
			if(rs.next()) { 
				DBInfoBean oneRow = new DBInfoBean();
				long assemblyId = rs.getLong(1);
				String deviceCode = rs.getString(2);
				String sn2 = rs.getString(3);
				Long subWorkOrder = rs.getLong(4);
				String status = rs.getString(5);
				String productType = rs.getString(6);
				String LineFlag = rs.getString(7);
				Long workOrderId = rs.getLong(8);
			
				oneRow.setAssemblyId(assemblyId);
				oneRow.setDeviceCode(deviceCode);
				oneRow.setSn2(sn2);
				oneRow.setSubWorkOrderId(subWorkOrder);
				oneRow.setStatus(status);
				oneRow.setProductType(productType);
				oneRow.setLineFlag(LineFlag);
				oneRow.setWorkOrderId(workOrderId);
				return oneRow;
			}
			
		} catch (SQLException e) {
			log.error( "" ,e );
		}finally {
			PmgrDB.closeRes(ps , rs);
		}
		return null;
	}



	//SN - > （工单号-行号  类型）
	public static List<DBInfoBean> getInfoForCards( String[] sns ){
		
		Connection conn = PmgrDB.getConn();
		
		List<DBInfoBean> queryBaseInfo = queryBaseInfos( sns  , conn);
		log.debug( "基础信息个数 ： " + queryBaseInfo.size() );
		if( !CollectionUtils.isEmpty(queryBaseInfo) ) {
			
			queryWorkOrderInfos(  queryBaseInfo , conn );
			queryProdInfos(  queryBaseInfo , conn );
		}
		PmgrDB.closeRes(conn);
		
		return queryBaseInfo;
	}
	
	
	private static void queryProdInfos(List<DBInfoBean>  baseInfos, Connection conn) {
		
		List<String> pts = new ArrayList<>();
		for( DBInfoBean bean :  baseInfos   ) {
			String productType = bean.getProductType();
			pts.add(productType);
		}
		String inCriteria = createInCriteriaStrByStrList(pts);
		
		String sql = " select pt.productNo , pt.prodtype, pt.proddscr from producttype pt where productNo in " +inCriteria;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			Map<String, DBInfoBean> idToBean = new HashMap<String, DBInfoBean>();
			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(  rs.next()   ) {
				DBInfoBean oneRow = new DBInfoBean();
				String productType = rs.getString(1);
				String prodType = rs.getString(2);
				String prodDscr = rs.getString(3);
				
				oneRow.setProductType(productType);
				oneRow.setProdType(prodType);
				oneRow.setProdDscr(prodDscr);
				
				idToBean.put(productType, oneRow);
			}
			
			for( DBInfoBean bean :  baseInfos   ) {
				String key = bean.getProductType();
				DBInfoBean bdInfoBean = idToBean.get(key);
				
				bean.setProdType(bdInfoBean.getProdType());
				bean.setProdDscr(bdInfoBean.getProdDscr());
			}
			
		} catch (SQLException e) {
			log.error( "" ,e );
		}finally {
			PmgrDB.closeRes(ps , rs);
		}
		log.debug( "============ : "  + baseInfos.size());
	}



	private static void queryWorkOrderInfos( List<DBInfoBean> baseInfos  , Connection conn ) {
		
		List<Long> wordOrderIds = new ArrayList<>();
		for( DBInfoBean bean :  baseInfos   ) {
			Long woId = bean.getWorkOrderId();
			wordOrderIds.add(woId);
		}
		
		String inCriteria = createInCriteriaStrByLongList(wordOrderIds);
		String sql = "	select od.id , od.WorkOrderNo , od.Create_Time from assemblyworkorder od where id in   " +inCriteria;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Map<String, DBInfoBean> woIdToBean = new HashMap<String, DBInfoBean>();
			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(  rs.next()   ) {
				DBInfoBean oneRow = new DBInfoBean();
				long odId = rs.getLong(1);
				String workOrderNo = rs.getString(2);
				Date date = rs.getDate(3 );
				oneRow.setWorkOrderId(odId);
				oneRow.setWorkOrderNo(workOrderNo);
				oneRow.setCreate_Time(date);
				
				woIdToBean.put(odId+"", oneRow);
			}
			
			for( DBInfoBean bean :  baseInfos   ) {
				String key = bean.getWorkOrderId() + "";
				DBInfoBean bdInfoBean = woIdToBean.get(key);
				
				bean.setWorkOrderNo(bdInfoBean.getWorkOrderNo() );
				bean.setCreate_Time( bdInfoBean.getCreate_Time());
			}
			
		} catch (SQLException e) {
			log.error( "" ,e );
		}finally {
			PmgrDB.closeRes(ps , rs);
		}
		log.debug( "============ : "  + baseInfos.size());
		
	}



	public static List<DBInfoBean> queryBaseInfos( String[] sns , Connection conn) {
		String snInCriteria = createInCriteriaStrByStrArr(sns);
		String sql = "	select ap.id , ap.deviceCode , ap.productNo, ap.subWorkOrder,ap.status , subo.productType , subo.LineFlag,subo.workOrder " + 
						" from assemblyproduct ap , assemblysubworkorder subo "+
						"where (ap.deviceCode in " + snInCriteria + " or ap.productNo in "+snInCriteria+")"+
						" and subo.sid=ap.subWorkOrder ";
		
		List<DBInfoBean> ret = new ArrayList<DBInfoBean>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(  rs.next()   ) {
				DBInfoBean oneRow = new DBInfoBean();
				long assemblyId = rs.getLong(1);
				String deviceCode = rs.getString(2);
				String sn2 = rs.getString(3);
				Long subWorkOrder = rs.getLong(4);
				String status = rs.getString(5);
				String productType = rs.getString(6);
				String LineFlag = rs.getString(7);
				Long workOrderId = rs.getLong(8);
			
				oneRow.setAssemblyId(assemblyId);
				oneRow.setDeviceCode(deviceCode);
				oneRow.setSn2(sn2);
				oneRow.setSubWorkOrderId(subWorkOrder);
				oneRow.setStatus(status);
				oneRow.setProductType(productType);
				oneRow.setLineFlag(LineFlag);
				oneRow.setWorkOrderId(workOrderId);
				
				ret.add(oneRow);
			}
			
		} catch (SQLException e) {
			log.error( "" ,e );
		}finally {
			PmgrDB.closeRes(ps , rs);
		}
		
		return ret;
		
	}

	
	public static boolean passTest( List<DBInfoBean> baseInfos ) {
		List<Long> ids = new ArrayList<>();
		for( DBInfoBean bean :  baseInfos   ) {
			Long woId = bean.getAssemblyId();
			ids.add(woId);
		}
		String inCriteria = createInCriteriaStrByLongList(ids);
		String sql = "	update assemblyproduct set status = -1 where  id in " +inCriteria;
		PreparedStatement ps = null;
		Connection conn = PmgrDB.getConn();
		try {
			ps = conn.prepareStatement(sql);
			int executeUpdate = ps.executeUpdate();
			if( baseInfos.size() ==   executeUpdate) {
				return true;
			}
		} catch (SQLException e) {
			log.error( "" ,e );
		}finally {
			PmgrDB.closeRes(ps , null, conn);
		}
		return false;
	}
	
	
	
	

	


	public static  List<String> strArrToList(  String[]  arr  ){
		List<String> list = new ArrayList<>();
		for( String e :   arr ) {
			list.add(e);
		}
		return list;
	}
	
	public static  String[] listToArr(  List<String> list  ){
		String[] ret = new String[list.size() ];
		int size = list.size();
		for(  int i=0; i < size ;i++ ) {
			ret[i] = list.get(i);
		}
		return ret;
	}
	
	
	
	public static String createInCriteriaStrByLongList( Collection<Long> flags ){
		
		if( flags== null || flags.size()==0 ){
			return " ( ) ";
		}
		int size = flags.size();
		int counter = 0 ;
		StringBuilder inCriteria = new StringBuilder( " ( " );
		Iterator<Long> iterator = flags.iterator();
		while(  iterator.hasNext() ){
			Long next = iterator.next();
			if( counter ==  size-1){
				inCriteria.append( next +" ");
			}else{
				inCriteria.append( next +", ");
			}
			counter++;
		}
		inCriteria.append(" ) ");
		
		return inCriteria.toString();
		
	}
	
	public static String createInCriteriaStrByStrList( Collection<String> flags ){
		
		if( flags== null || flags.size()==0 ){
			return " ( ) ";
		}
		int size = flags.size();
		int counter = 0 ;
		StringBuilder inCriteria = new StringBuilder( " ( " );
		Iterator<String> iterator = flags.iterator();
		while(  iterator.hasNext() ){
			String next = iterator.next();
			if( counter ==  size-1){
				inCriteria.append( " '" +next +"' ");
			}else{
				inCriteria.append(  " '" +next +"', ");
			}
			counter++;
		}
		inCriteria.append(" ) ");
		
		return inCriteria.toString();
	}
	
	
	
	public static String createInCriteriaStrByStrArr( String[] flags ){
		
		if( flags== null || flags.length==0 ){
			return " ( ) ";
		}
		StringBuilder inCriteria = new StringBuilder( " ( " );
		for(  int i=0; i<flags.length ;i++   ) {
			String next = flags[i];
			if( i ==  flags.length-1 ){
				inCriteria.append( " '" +next +"' ");
			}else{
				inCriteria.append(  " '" +next +"', ");
			}
		}
		inCriteria.append(" ) ");
		
		return inCriteria.toString();
	}


	public static boolean addTestLog(TestLog testLog){

		Connection con = null;
		String sql = "insert into testLog(bdsn,descr,oemsn,res,timeStamp ,workOrder,mac) values(?,?,?,?,?,?,?)";
		PreparedStatement pstmt = null;
		try {
			con = AutoTestDB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, testLog.getBdSn());
			pstmt.setString(2, testLog.getDescr());
			pstmt.setString(3, testLog.getOemSn());
			pstmt.setString(4, testLog.getRes());
			pstmt.setLong(5, testLog.getTimeStamp());
			pstmt.setString(6, testLog.getWorkOrder());
            pstmt.setString(7, testLog.getMac());
			int rows = pstmt.executeUpdate();
			if(rows > 0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error("",e);
		} finally {
			AutoTestDB.close(con, pstmt, null);
		}
		return false;

	}

	public static List<TestLog> queryTestLogList(String workOrder){
		List<TestLog> list = new ArrayList<TestLog>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = AutoTestDB.getConnection();
			List<Object> params = new ArrayList<>();
			StringBuffer sb = new StringBuffer("select * from testlog where  workOrder = '"+workOrder+"' ");
			sb.append(" order by timeStamp desc");
			pstmt = con.prepareStatement(sb.toString());
			rs = pstmt.executeQuery();
			while(rs.next()){
				TestLog testLog = new TestLog();
				testLog.setWorkOrder(rs.getString("workOrder"));
				testLog.setBdSn(rs.getString("bdSn"));
				testLog.setOemSn(rs.getString("oemSn"));
				testLog.setMac(rs.getString("mac"));
				testLog.setRes(rs.getString("res"));
				testLog.setDescr(rs.getString("descr"));
				testLog.setTimeStamp(rs.getLong("timeStamp"));
                testLog.setDate(testLog.formatDate(rs.getLong("timeStamp")));
				list.add(testLog);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.error("",e);
		}finally {
			AutoTestDB.close(con, pstmt, rs);
		}
		return list;


	}

	public static void main(String[] args) {

//		String [] sns = new String[] {  "0020080800105"   };
//		System.out.println(getInfoForCards(sns));
		TestLog testLog = new TestLog();
		testLog.setOemSn("aaaaaa");
		testLog.setBdSn("bbbbb");
		testLog.setWorkOrder("ccccc");
		testLog.setRes("false");
		testLog.setMac("aaaaaaaaa");
		testLog.setTimeStamp(System.currentTimeMillis());
		for(int i=0;i<100;i++){
			addTestLog(testLog);
		}
		queryTestLogList("ccccc");
	}





}
